Connecting to Availability Groups

Introduction

During this lab, you will learn how to connect to Availability Groups using the listener name and connecting to individual replicas using the replica name utilizing SQL Server Management Studio (SSMS).

Objectives

At the end of this lab, you will be able to:

  • Identify and connect to the listener
  • Connect to individual secondary replicas

Estimated Time

15 minutes

Logon Information

Before Login make sure windows has Applied Computer Setting to all nodes.

Use the following credentials to login into virtual environment

  1. Connect to AlwaysOnClient as Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  2. Connect to AlwaysOnN1 as CORPNET\Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  3. Connect to AlwaysOnN2 as CORPNET\Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  4. Connect to AlwaysOnN3 as CORPNET\Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  5. Connect to AlwaysOnDC as Administrator using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  6. Change the screen resolution if required.

    You may want to adjust the screen resolution to your own preference. Do this by right-clicking on the desktop and choosing Screen resolution and clicking OK when finished.

Exercise 1: Connect directly to a replica

This exercise shows how to connect to any replica in an availability group.

Tasks

  1. Login to the AlwaysOnClient virtual machine as CORPNET\cluadmin using Pa$$w0rd as the password.

    Click the Type Text icon to enter the associated text into the virtual machine.

  2. Open SQL Server Management Studio (SSMS)

    1. Log into the AlwaysOnClient server.

    2. Open SSMS by left clicking the icon on the taskbar.

  3. Connect to replica AlwaysOnN1

    1. In the Connect to Server dialog box, type in AlwaysOnN1 and then click Connect.

      While this connection should work without issue due to the changes made in the .NET provider, in rare instances or in your production environment there may be a need to specifically add in the MultiSubnetFailover=True keyword to the connection string.

      This can be added using SQL Server Management Studio (SSMS) by clicking the "Options" button and typing in "MultiSubnetFailover=True" into the textbox presented.

      In older versions of SSMS (before SSMS 2016) the extra connection information was not saved as part of the session. In newer version of SSMS (2016+) this information is saved as part of the connection and all subsequent connections will use this by default.

  4. Discover AlwaysOn information and metadata

    1. Expand the AlwaysOn High Availability Folder

    2. Expand the Availability Group Folder

    3. How many availability groups does this replica support? Is there any word after the availability group name - if so, what is it?

    4. Expand the AGCorp availability group

    5. Expand the Availability Replicas Folder

    6. How many replicas are involved in this availability group?

    7. Expand the Availability Databases Folder

    8. How many Databases are involved in this availability group?

    9. Expand the Availability Group Listeners Folder

    10. How many listeners are there for this availability group and what are their DNS names?

Congratulations!

You have successfully completed this exercise. Click Next to advance to the next exercise.

Exercise 2: Connect to the listener

This exercise shows how to connect to the listener, which will always point to the primary replica.

Tasks

  1. Connect to the primary replica

    1. Open a new server connection by left clicking on the Connect button in object explorer and choosing Database Engine…

    2. Type in the listener name found in Exercise 1, task 3 AGCorpListen and click connect.

  2. Check the name of the replica

    1. Open a new query window by pressing the New Query button on the toolbar or pressing Control+N.

    2. Type in the query window: SELECT @@SERVERNAME

    3. We connected to AGCorpListen but what is the actual primary replica?

    4. Where else could we find which replica is the primary without connecting to SQL Server?

Congratulations!

You have successfully completed this exercise. Click Next to advance to the next exercise.

(Optional) Exercise 3: Connect using SQLCMD

This exercise shows how to connect to an availability group using SQLCMD.

Tasks

  1. Open a Command Prompt or PowerShell window

    Right-click on the windows icon in the bottom left corner of the window and choose Windows Powershell (Admin).

  2. If a User Account Control (UAC) prompt appears, press Yes.

  3. Type into the powershell window: 

    PowerShell
    sqlcmd /?

    This will list out all of the sqlcmd option for the version of sqlcmd installed on the system.

  4. Notice two of the options: M and K - What do these switches do?

    M:

    K:

  5. Construct a proper connection through SQLCMD by using the listener name and the M option.

    1. Type into the Powershell Window: 

      PowerShell
      SQLCMD -S AGCorpListen -M

    2. Press enter to connect, you should now be connected and presented with a prompt that says 1>

    3. Select the server name by typing in: 

      PowerShell
      SELECT @@SERVERNAME

    4. Press enter, then type in GO, and press enter.

    5. What is the result?

    6. Type in exit, press enter, then close the Powershell Window.

Congratulations!

You have successfully completed this exercise. You can move to the next lab.